use std::time::Duration;
use std::collections::HashMap;
use lazy_static::lazy_static;
use mysql::{
    Compression, OptsBuilder, Params, Pool,
    Row, Value, TxOpts, Result,
    prelude::Queryable
};
use crate::rules::Vo;

const DB_NAME: &str = "pss_dev";

lazy_static! {
    static ref POOL: Pool = {
        let attrs = map! {
            "characterEncoding" => "utf8",
            "tinyInt1isBit" => "false",
            "yearIsDateType" => "false",
            "rewriteBatchedStatements" => "true",
            "useServerPrepStmts" => "true",
            "cachePrepStmts" => "true",
            "prepStmtCacheSize" => "250",
            "prepStmtCacheSqlLimit" => "2048"
        };
        let opts = OptsBuilder::new()
            .ip_or_hostname(Some("127.0.0.1"))
            .tcp_port(3306_u16)
            .user(Some("root"))
            .pass(Some("china"))
            .db_name(Some(DB_NAME))
            .compress(Some(Compression::best()))
            .tcp_keepalive_time_ms(Some(28800000))
            .tcp_connect_timeout(Some(Duration::from_millis(60000)))
            .connect_attrs(attrs);
        Pool::new(opts).unwrap()
    };
}

#[inline(always)]
pub fn count(sql: &str, params: &[Value]) -> u32 {
    let mut conn = POOL.get_conn().unwrap();
    let stmt = conn.prep(sql).unwrap();
    let num: u32 = conn
        .exec_first(&stmt, Params::Positional(params.to_vec()))
        .unwrap()
        .unwrap();

    num
}

#[inline(always)]
pub fn list(sql: &str, params: &[Value]) -> Vec<Vo> {
    let mut conn = POOL.get_conn().unwrap();
    let stmt = conn.prep(sql).unwrap();
    let rows: Vec<Row> = conn
        .exec(&stmt, Params::Positional(params.to_vec()))
        .unwrap();
    let mut list = Vec::with_capacity(rows.len());
    for row in rows {
        list.push(row2map(&row));
    }
    
    list
}

#[inline(always)]
pub fn get(sql: &str, params: &[Value]) -> Vo {
    let mut conn = POOL.get_conn().unwrap();
    let stmt = conn.prep(sql).unwrap();
    let option: Option<Row> = conn
        .exec_first(&stmt, Params::Positional(params.to_vec()))
        .unwrap();
    match option {
        None => Vo {
            data: HashMap::new(),
        },
        Some(row) => row2map(&row),
    }
}

#[inline(always)]
fn row2map(row: &Row) -> Vo {
    let cap = ((row.len() as f32 / 0.7) + 0.5) as usize;
    let mut map = HashMap::with_capacity(cap);
    for col in row.columns_ref() {
        map.insert(
            String::from(col.name_str()),
            to_str(&row[col.name_str().as_ref()]),
        );
    }
    
    Vo { data: map }
}

#[inline(always)]
fn to_str(val: &Value) -> String {
    match val {
        Value::NULL => "".into(),
        Value::Int(x) => format!("{}", x),
        Value::UInt(x) => format!("{}", x),
        Value::Float(x) => format!("{}", x),
        Value::Double(x) => format!("{}", x),
        Value::Date(y, m, d, 0, 0, 0, 0) => format!("{:04}-{:02}-{:02}", y, m, d),
        Value::Date(year, month, day, hour, minute, second, 0) => format!(
            "{:04}-{:02}-{:02} {:02}:{:02}:{:02}",
            year, month, day, hour, minute, second
        ),
        Value::Date(year, month, day, hour, minute, second, micros) => format!(
            "{:04}-{:02}-{:02} {:02}:{:02}:{:02}.{:06}",
            year, month, day, hour, minute, second, micros
        ),
        Value::Bytes(ref bytes) => match std::str::from_utf8(&*bytes) {
            Ok(string) => String::from(string),
            Err(_) => {
                let mut s = String::from("0x");
                for c in bytes.iter() {
                    s.extend(format!("{:02X}", *c).chars())
                }
                s
            }
        },
        _ => "".into(),
    }
}

#[inline(always)]
pub fn set_params(table_name: &str, vo: &Vo) -> HashMap<String, Value> {
    let params = vec![Value::from(DB_NAME), Value::from(table_name)];
    let sql = "select column_name,data_type from information_schema.columns where table_schema=? and table_name=?";
    let mut conn = POOL.get_conn().unwrap();
    let stmt = conn.prep(&sql).unwrap();
    let rows: Vec<Row> = conn.exec(&stmt, Params::Positional(params)).unwrap();
    let cap = ((rows.len() as f32/0.7)+0.5) as usize;
    let mut des = HashMap::with_capacity(cap);
    for row in rows {
        let col = to_str(&row["column_name"]);
        let types = to_str(&row["data_type"]);
        if vo.some(col.as_str()) {
            des.insert(col.clone(), to_value(col.clone(), &types, &vo));            
        }
    }
    
    des
}

#[inline(always)]
fn to_value(col: String, types: &str, vo: &Vo) -> Value {
    let col = col.as_str();
    match types {
        "varchar" => Value::from(vo.str(col)),
        "int" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::from(vo.long(col))
            }
        }
        "tinyint" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::from(vo.int(col))
            }
        }
        "double" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::Double(vo.double(col))
            }
        }
        "decimal" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::Double(vo.double(col))
            }
        }
        "date" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::from(vo.str(col))
            }
        }
        "datetime" => {
            if vo.blank(col) {
                Value::NULL
            } else {
                Value::from(vo.str(col))
            }
        }
        _ => Value::from(vo.str(col)),
    }
}

#[inline(always)]
pub fn save(table_name: &str, map: &HashMap<String, Value>) -> u32 {
    let mut params = Vec::new();
    let mut cols = String::new();
    let mut vals = String::new();
    for (key, val) in map.iter() {
        cols.push(',');
        cols.push_str(key);
        vals.push_str(",?");
        params.push(val.to_owned());
    }

    let sql = format!(
        "insert into {}({}) values ({})",
        table_name,
        cols.trim_start_matches(','),
        vals.trim_start_matches(',')
    );
    let mut conn = POOL.get_conn().unwrap();
    let stmt = conn.prep(&sql).unwrap();
    conn.exec::<Row, _, _>(&stmt, Params::Positional(params))
        .unwrap();
    
    conn.last_insert_id() as u32
}

#[inline(always)]
pub fn update(table_name: &str, map: &HashMap<String, Value>) -> Result<bool> {
    let mut id = Value::NULL;
    let mut params = Vec::new();
    let mut cols = String::new();
    for (key, val) in map.iter() {
        if key == "id" {
            id = val.to_owned();
        } else {
            cols.push(',');
            cols.push_str(key);
            cols.push_str("=?");
            params.push(val.to_owned());
        }
    }
    params.push(id);

    let sql = format!(
        "update {} set {} where id=?",
        table_name,
        cols.trim_start_matches(','),
    );
    let mut conn = POOL.get_conn()?;
    let stmt = conn.prep(&sql)?;
    conn.exec::<Row, _, _>(&stmt, Params::Positional(params))?;
    
    Ok(true)
}

pub fn batch(sql: &str, paras: &[Vec<Value>]) -> Result<bool> {
    let mut conn = POOL.get_conn()?;
    let stmt = conn.prep(&sql)?;
    let mut tx = conn.start_transaction(TxOpts::default())?;
    for para in paras.iter() {
        tx.exec::<Row, _, _>(&stmt, Params::Positional(para.to_vec()))?;
    }
    let _ = tx.commit();
    
    Ok(true)
}

pub fn batch2sql(before_sql: &str, para: &[Value], after_sql: &str, paras: &[Vec<Value>]) -> Result<bool> {
    let mut conn = POOL.get_conn()?;
    let mut tx = conn.start_transaction(TxOpts::default())?;
    tx.exec::<Row, _, _>(&before_sql, Params::Positional(para.to_vec()))?;        
    for para in paras.iter() {
        tx.exec::<Row, _, _>(&after_sql, Params::Positional(para.to_vec()))?;
    }
    let _ = tx.commit();
    
    Ok(true)
}
